package com.mustafaiev.tair.autobase.dao;

import java.io.Serializable;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import sun.misc.BASE64Encoder;

import com.mustafaiev.tair.autobase.db.DBConnector;
import com.mustafaiev.tair.autobase.worker.Worker;

public class WorkerDaoImpl implements WorkerDao, Serializable {

    private DBConnector connector;
    private static final Logger LOG = Logger.getLogger(WorkerDaoImpl.class.getName());
    /**
     * Identificator for position Manager
     */
    private static final String MANAGER_POSITION_ID = "1";

    /**
     * Identificator for position Driver
     */
    private static final String DRIVER_POSITION_ID = "2";

    /**
     * Identificator for position Super Administrator
     */
    private static final String SADMIN_POSITION_ID = "3";

    @Override
    public void setConnector(DBConnector connector) {
        this.connector = connector;
    }

    @Override
    public boolean addNewWorker(Worker worker) throws SQLException {

        // if worker instance is null then return 0
        if (worker == null) {
            LOG.error("Worker is null");
            return false;

        }

        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = connector.getConnection();
            statement = connection.prepareStatement("INSERT INTO " + getTableName()
                    + " (ID, POSITION_ID, NAME, SURNAME, EMAIL, PASSWORD, BLOCK)"
                    + " VALUES (?, ?, ?, ?, ?, ?, ?)");
            // set id
            statement.setNull(1, java.sql.Types.BIGINT);
            // set worker position id
            statement.setInt(2, worker.getPositionId());
            // set worker name
            statement.setString(3, worker.getName());
            // set worker surname
            statement.setString(4, worker.getSurname());
            // set worker email
            statement.setString(5, worker.getEmail());
            // set worker password
            if (worker.getPassword() != null) {
                statement.setString(6, encodePassword(worker.getPassword()));
            } else {
                return false;
            }
            // set worker block value
            statement.setInt(7, 1);

            // condition creates some doubts...
            if (statement.executeUpdate() == 1 || statement.executeUpdate() == 0) {
                return true;
            } else {
                return false;
            }
        } finally {
            connector.close(statement);
            connector.close(connection);
        }
    }

    @Override
    public ResultSet getWorkerInformation(Worker worker) throws SQLException {

        return null;
    }

    @Override
    public List<Worker> getAllWorkers() throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Worker> workers = new ArrayList<Worker>();
        try {
            connection = connector.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM " + getTableName());

            while (resultSet.next()) {
                Worker worker = new Worker();
                worker.setId(resultSet.getInt("ID"));
                worker.setPositionId(resultSet.getInt("POSITION_ID"));
                worker.setName(resultSet.getString("NAME"));
                worker.setSurname(resultSet.getString("SURNAME"));
                worker.setEmail(resultSet.getString("EMAIL"));
                worker.setBlock(resultSet.getInt("BLOCK"));

                workers.add(worker);
            }
            return workers;
        } finally {
            connector.close(resultSet);
            connector.close(statement);
            connector.close(connection);
        }
    }

    @Override
    public List<Worker> getDrivers() throws SQLException {

        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<Worker> workers = new ArrayList<Worker>();
        try {
            connection = connector.getConnection();
            statement = connection.createStatement();
            resultSet = statement.executeQuery("SELECT * FROM " + getTableName()
                    + " WHERE POSITION_ID = " + DRIVER_POSITION_ID);

            while (resultSet.next()) {
                Worker worker = new Worker();
                worker.setId(resultSet.getInt("ID"));
                worker.setPositionId(resultSet.getInt("POSITION_ID"));
                worker.setName(resultSet.getString("NAME"));
                worker.setSurname(resultSet.getString("SURNAME"));
                worker.setEmail(resultSet.getString("EMAIL"));
                worker.setBlock(resultSet.getInt("BLOCK"));

                workers.add(worker);
            }
            return workers;
        } finally {
            connector.close(resultSet);
            connector.close(statement);
            connector.close(connection);
        }
    }

    @Override
    public int updateWorkerInformation(Worker worker) throws SQLException {

        return 0;
    }

    @Override
    public int deleteWorker(Worker worker) throws SQLException {

        return 0;
    }

    @Override
    public int blockWorker(Worker worker) throws SQLException {

        return 0;
    }

    @Override
    public int unblockWorker(Worker worker) throws SQLException {

        return 0;
    }

    private static String getTableName() {
        return "AUT_WORKERS";
    }

    /**
     * Method which encodes password into base64 charset
     * 
     * @param password
     * @return String base64 encoded password
     */
    private static String encodePassword(String password) {
        BASE64Encoder base64Encoder = new BASE64Encoder();
        byte[] input = password.getBytes();
        String passHash = base64Encoder.encode(input);
        return passHash;
    }

    @Override
    public boolean checkWorkerByEmail(Worker worker) throws SQLException {

        if (worker == null) {
            LOG.error("Object worker is null in process method checkWorkerByEmail()");
            return false;
        }
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = connector.getConnection();
            statement = connection.prepareStatement("SELECT * FROM " + getTableName()
                    + " WHERE EMAIL = ?");
            if (worker.getEmail() != null) {
                statement.setString(1, worker.getEmail());
            }else{
                return false;
            }
            if (statement.executeQuery().first()) {
                return true;
            } else {
                return false;
            }
        } finally {
            connector.close(statement);
            connector.close(connection);
        }
    }

    @Override
    public boolean checkWorkerByEmailAndPassword(Worker worker) throws SQLException {

        if (worker == null) {
            LOG.error("Object worker is null in process method checkWorkerByEmailAndPassword()");
            return false;
        }
        Connection connection = null;
        PreparedStatement statement = null;
        try {
            connection = connector.getConnection();
            statement = connection.prepareStatement("SELECT * FROM " + getTableName()
                    + " WHERE EMAIL = ? AND PASSWORD = ? ");
            if (worker.getEmail() != null) {
                statement.setString(1, worker.getEmail());
            } else {
                return false;
            }
            if (worker.getPassword() != null) {
                statement.setString(2, encodePassword(worker.getPassword()));
            } else {
                return false;
            }
            if (statement.executeQuery().first()) {
                return true;
            } else {
                return false;
            }

        } finally {
            connector.close(statement);
            connector.close(connection);
        }
    }
}
